﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Xml.Linq;
using System.Collections;

using System.IO;
using System.Reflection;
using System.Data;

using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace BlockedProcessMonitor
{
    public class Tracer
    {
        private CommandLineArgs cmdargs;
        private Server srvr;
        private TraceServer traceServerReader;
        private SqlConnectionInfo sci;
        private string traceTemplateFileName = "BlockedProcessTrace.tdf";
        private string outputFileName = "sqlblockedprocessmon_" + DateTime.Now.ToString("yyyyMMdd_hhmm")+".xml";
        private StreamWriter output;

        public Tracer(string[] args)
        {
            cmdargs = new CommandLineArgs(args);
        }

        public void Run()
        {
            if (cmdargs["?"] != null)
            {
                Console.WriteLine(string.Format(Properties.Resources.HelpInfo, Assembly.GetExecutingAssembly().GetName().Version.ToString()));
            }
            else
            {
                ProcessArgs();

                output = new StreamWriter(outputFileName);
                output.WriteLine("<blockedprocesses>");

                SQLTraceLiveReader();

                output.WriteLine("</blockedprocesses>");
                output.Flush();
                output.Close();

                try
                {
                    srvr.Configuration.BlockedProcessThreshold.ConfigValue = 0;
                    srvr.Configuration.Alter();
                }
                catch
                {
                }
            }
        }

      
        private void ProcessArgs()
        {
            // Look for specific arguments values and display 
            // them if they exist (return null if they don't)

            if (cmdargs["S"] != null)
            {
                Console.WriteLine("Connecting to Server : " + cmdargs["S"]);
                sci = new SqlConnectionInfo(cmdargs["S"]);
            }
            else
            {
                // Use the local SQL Server
                sci = new SqlConnectionInfo();
                Console.WriteLine("Server not defined! Connecting to localhost.");
            }

            if (cmdargs["E"] != null || (cmdargs["U"] == null && cmdargs["P"] == null))
            {
                Console.WriteLine("Connecting using Trusted Connection!" + cmdargs["E"]);
                sci.UseIntegratedSecurity = true;
            }

            if (cmdargs["E"] == null && (cmdargs["U"] != null && cmdargs["P"] != null))
            {
                Console.WriteLine("Connecting using specified credentials!" + cmdargs["U"] + "/" +cmdargs["P"]);
                sci.UserName = cmdargs["U"];
                sci.Password = cmdargs["P"];

            }

            if (cmdargs["O"] != null)
            {
                outputFileName = cmdargs["O"];
                Console.WriteLine("Writing output to : " + cmdargs["O"]);
            }
            else
                Console.WriteLine("Output filename not defined! Using default filename!");

            if (cmdargs["T"] != null)
            {
                traceTemplateFileName = cmdargs["T"];
                Console.WriteLine("Using Template: " + cmdargs["T"]);
            }
            else
                Console.WriteLine("Template not defined! Using default template!");

        }

        /// <summary>
        /// Configure the trace to run against the local SQL Server.
        /// </summary>
        private void SQLTraceLiveReader()
        {
            // Setup connection to the SQL Server
            traceServerReader = new TraceServer();

            // Test for SQL Express
            srvr = new Server(sci.ServerName);

            try
            {

                if (srvr.Information.Edition != @"Express Edition")
                {
                    srvr.Configuration.BlockedProcessThreshold.ConfigValue = 5;
                    srvr.Configuration.Alter();

                    // Configure the reader
                    // Use the Standard profiler configuration
                    // string programFilesPath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
                    traceServerReader.InitializeAsReader(sci, traceTemplateFileName);

                    // Start reading the trace information
                    SQLTraceReader(traceServerReader);
                }
                else
                {
                    Console.WriteLine("SQL Express is not supported for tracing");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// Read and display the trace information.
        /// </summary>
        /// <param name="dataReader"></param>
        private void SQLTraceReader(IDataReader dataReader)
        {
            ConsoleKeyInfo cki;

            try
            {
                while (dataReader.Read())
                {
                    int textdata = dataReader.GetOrdinal("TextData");
                    int eventclass = dataReader.GetOrdinal("EventClass");

                    if (dataReader.GetValue(eventclass).ToString() == "Blocked process report")
                    {
                        XDocument xdoc = XDocument.Parse(dataReader.GetValue(textdata).ToString());
                        ProcessTraceEvent(xdoc);
                        output.WriteLine(xdoc.ToString());
                        output.Flush();
                    }

                    // Watch for the user to press the ESC key and terminate while loop
                    if (Console.KeyAvailable == true)
                    {
                        cki = Console.ReadKey(true);
                        if (cki.Key == ConsoleKey.Escape)
                        {
                            break;
                        }
                    }
                }
            }
            finally
            {
                // Close the reader
                dataReader.Close();
            }
        }

        private void ProcessTraceEvent(XDocument xdoc)
        {
            ProcessReportElement(xdoc.Root.Element("blocked-process").Element("process").Element("executionStack").Elements("frame"));
            ProcessReportElement(xdoc.Root.Element("blocking-process").Element("process").Element("executionStack").Elements("frame"));
            System.Threading.Thread.Sleep(1000);
        }

        private void ProcessReportElement(IEnumerable<XElement> element)
        {
            foreach (XElement xe in element)
            {
                string sqlhandle = null;
                int stmtstart = -1;
                int stmtend = -1;

                if (xe.Attribute("sqlhandle") != null)
                {
                    sqlhandle = xe.Attribute("sqlhandle").Value;
                    if (xe.Attribute("stmtstart") != null)
                        stmtstart = Int32.Parse(xe.Attribute("stmtstart").Value);
                    if (xe.Attribute("stmtend") != null)
                        stmtend = Int32.Parse(xe.Attribute("stmtend").Value);

                    if (sqlhandle != null)
                    {
                        XElement newxe = new XElement("sqlstatement", new XAttribute("text", GetSqlStatement(sqlhandle, stmtstart, stmtend)));
                        xe.Add(newxe);
                    }
                }
            }
        }
        private string GetSqlStatement(string sqlhandle, int stmtstart, int stmtend)
        {
            string ret = (string)srvr.ConnectionContext.ExecuteScalar(string.Format(@"select text from sys.dm_exec_sql_text({0})", sqlhandle));
            if (ret == null)
                ret = string.Empty;
            if (stmtstart != -1 && stmtend != -1)
                ret = ret.Substring(stmtstart / 2, stmtend / 2 - stmtstart / 2);
            else if (stmtstart != -1 && stmtend == -1)
                ret = ret.Substring(stmtstart / 2);
            return ret;
        }

    }
}
